package com.fanfq.util;

import java.util.ArrayList;
import java.util.List;

import com.fanfq.itrack.Gps;
import com.fanfq.itrack.model.Ride;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.Toast;

public class DBUtil {

	private static final String DB_NAME = "itrack";
	private static final String TAB_GPS = DB_NAME + "_gps";
	private static final String TAB_RIDE = DB_NAME + "_ride";

	public final Context mContext;
	private DatabaseHelper mDatabaseHelper;
	private SQLiteDatabase mSQLiteDatabase;

	private static class DatabaseHelper extends SQLiteOpenHelper {

		public DatabaseHelper(Context context) {
			super(context, DB_NAME, null, 1);
		}

		@Override
		public void onCreate(SQLiteDatabase sdb) {
			// 建表
			sdb.execSQL("create table "
					+ TAB_GPS
					+ " (ride_id TEXT NOT NULL, pos INTEGER, timestamp LONG, latiude DOUBLE, longitude DOUBLE, altitude DOUBLE, h_accuracy DOUBLE, v_accuracy DOUBLE, description TEXT, speed DOUBLE, bearing DOUBLE, device_time LONG, filtered INTEGER, elapsed_time LONG, distance DOUBLE);");
			sdb.execSQL("create table "
					+ TAB_RIDE
					+ " (id TEXT NOT NULL, user_id TEXT, image TEXT, title TEXT, distance DOUBLE, avg_speed DOUBLE, elev_gain DOUBLE, max_elev DOUBLE, max_speed DOUBLE, avg_power DOUBLE, start_timestamp LONG, end_timestamp LONG, moving_time LONG, elapsed_time LONG, is_sync INTEGER, is_private INTEGER, kudos_count INTEGER, comments_count INTEGER, calories INTEGER);");
		}

		@Override
		public void onUpgrade(SQLiteDatabase sdb, int oldVersion, int newVersion) {
			sdb.execSQL("drop table if exists " + TAB_GPS);
			sdb.execSQL("drop table if exists " + TAB_RIDE);
			onCreate(sdb);
		}
	}

	/**
	 * @Title: DBUtil
	 * @Description: 初始化数据库
	 * @param Context
	 *            context
	 * @return void
	 * @throws
	 */
	public DBUtil(Context context) {
		mContext = context;
	}

	/**
	 * @Title: openDB
	 * @Description: 打开数据库
	 * @param void
	 * @return void
	 * @throws
	 */
	public void openDB() {
		mDatabaseHelper = new DatabaseHelper(mContext);
		mSQLiteDatabase = mDatabaseHelper.getWritableDatabase();
	}

	/**
	 * @Title: closeDB
	 * @Description: 关闭数据库
	 * @param void
	 * @return void
	 * @throws
	 */
	public void closeDB() {
		mDatabaseHelper.close();
	}

	/**
	 * @Title: getSQLiteDatabase
	 * @Description: 获取数据库实例
	 * @param void
	 * @return SQLiteDatabase
	 * @throws
	 */
	public SQLiteDatabase getSQLiteDatabase() {
		return mSQLiteDatabase;
	}

	public boolean addGpsData(Gps cdata) {
		this.openDB();
		boolean result = true;
		try {
			String sql = String
					.format("insert into "
							+ TAB_GPS
							+ " (ride_id,pos,timestamp,latiude,longitude,altitude,h_accuracy,v_accuracy,description,speed,bearing,device_time,filtered,elapsed_time,distance) values ('%s',%d,%d,%f,%f,%f,%f,%f,'%s',%f,%f,%d,%d,%d,%f);",
							cdata.getRide_id(), cdata.getPos(),
							cdata.getTimestamp(), cdata.getLatitude(),
							cdata.getLongitude(), cdata.getAltitude(),
							cdata.getH_accuracy(), cdata.getV_accuracy(),
							cdata.getDescription(), cdata.getSpeed(),
							cdata.getBearing(), cdata.getDevice_time(),
							cdata.getFiltered(), cdata.getElapsed_time(),
							cdata.getDistance());
			this.getSQLiteDatabase().execSQL(sql);
			result = true;
		} catch (SQLException e) {
			result = false;
			Toast.makeText(mContext, "保存GPS数据失败:" + e.getMessage(),
					Toast.LENGTH_LONG).show();
		}
		this.closeDB();
		return result;
	}

	public List<Gps> getGpsByRideId(String ride_id) {
		// ride_id,pos,timestamp,latiude,longitude,altitude,h_accuracy,v_accuracy,description,speed,bearing,device_time,filtered,elapsed_time,distance
		this.openDB();
		List<Gps> result = new ArrayList<Gps>();
		String sql = "select * from " + TAB_GPS + " where ride_id = '" + ride_id+"';";//
		Cursor c = this.getSQLiteDatabase().rawQuery(sql, null);
		Gps gps = null;
		if (c.getCount() != 0) {
			c.moveToFirst();
			while (!c.isAfterLast()) {
				gps = new Gps();
				gps.setRide_id(c.getString(1));
				gps.setPos(c.getInt(2));
				gps.setTimestamp(c.getLong(3));
				gps.setLatitude(c.getDouble(4));
				gps.setLongitude(c.getDouble(5));
				gps.setAltitude(c.getDouble(6));
				gps.setH_accuracy(c.getDouble(7));
				gps.setV_accuracy(c.getDouble(8));
				gps.setDescription(c.getString(9));
				gps.setSpeed(c.getDouble(10));
				gps.setBearing(c.getDouble(11));
				gps.setDevice_time(c.getLong(12));
				gps.setFiltered(c.getInt(13));
				gps.setElapsed_time(c.getLong(14));
				gps.setDistance(c.getDouble(15));
				result.add(gps);
				c.moveToNext();
			}
		}
		this.closeDB();
		return result;
	}

	public boolean addRide(Ride cdata) {
		this.openDB();
		boolean result = true;
		try {
			String sql = String
					.format("insert into "
							+ TAB_RIDE
							+ " (id, user_id, image, title, distance, avg_speed, elev_gain, max_elev, max_speed, avg_power, start_timestamp, end_timestamp, moving_time, elapsed_time, is_sync, is_private, kudos_count, comments_count, calories) values ('%s','%s','%s','%s',%f,%f,%f,%f,%f,%f,%d,%d,%d,%d,%d,%d,%d,%d,%d);",
							cdata.getId(), cdata.getUser_id(), cdata.getId(),
							cdata.getImage(), cdata.getTitle(),
							cdata.getDistance(), cdata.getAvg_speed(),
							cdata.getElev_gain(), cdata.getMax_speed(),
							cdata.getAvg_power(), cdata.getStart_timestamp(),
							cdata.getEnd_timestamp(), cdata.getMoving_time(),
							cdata.getElapsed_time(), cdata.getIs_sync(),
							cdata.getIs_private(), cdata.getKudos_count(),
							cdata.getComments_count(), cdata.getCalories());
			this.getSQLiteDatabase().execSQL(sql);
			result = true;
		} catch (SQLException e) {
			result = false;
			Toast.makeText(mContext, "保存RIDE数据失败:" + e.getMessage(),
					Toast.LENGTH_LONG).show();
		}
		this.closeDB();
		return result;
	}

	public List<Ride> getRidesByUserId(String user_id) {
		//id, user_id, image, title, distance, avg_speed, elev_gain, max_elev, max_speed, avg_power, start_timestamp, end_timestamp, 
		//moving_time, elapsed_time, is_sync, is_private, kudos_count, comments_count, calories) values (%s,%s,%s,%s,%f,%f,%f,%f,%f,%f,%d,%d,%d,%d,%d,%d,%d,%d,%d);",

		this.openDB();
		List<Ride> result = new ArrayList<Ride>();
		String sql = "select * from " + TAB_RIDE + " where user_id = '" + user_id+"';";//
		Cursor c = this.getSQLiteDatabase().rawQuery(sql, null);
		Ride ride = null;
		if (c.getCount() != 0) {
			c.moveToFirst();
			while (!c.isAfterLast()) {
				ride = new Ride();
				ride.setId(c.getString(1));
				ride.setUser_id(c.getString(2));
				ride.setImage(c.getString(3));
				ride.setTitle(c.getString(4));
				ride.setDistance(c.getDouble(5));
				ride.setAvg_speed(c.getDouble(6));
				ride.setElev_gain(c.getDouble(7));
				ride.setMax_elev(c.getDouble(8));
				ride.setMax_speed(c.getDouble(9));
				ride.setAvg_power(c.getDouble(10));
				ride.setStart_timestamp(c.getLong(11));
				ride.setEnd_timestamp(c.getLong(12));
				ride.setMoving_time(c.getLong(13));
				ride.setElapsed_time(c.getLong(14));
				ride.setIs_sync(c.getInt(15));
				ride.setIs_private(c.getInt(16));
				ride.setKudos_count(c.getInt(17));
				ride.setComments_count(c.getInt(18));
				ride.setCalories(c.getInt(19));
				result.add(ride);
				c.moveToNext();
			}
		}
		this.closeDB();
		return result;
	}
}
